<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Datareport_m extends CI_Model {
	function __construct()
    {
        parent::__construct();
    }

	function saleplatform_m($where,$salenum='saleNum',$nums=1,$type=0,$pages=1)
	{
		/*1|AMZ一组 
6|AMZ二组
7|AMZ三组
8|AMZ四组
9|AMZ五组
11|AMZ合作账户1
5|AMZ合作账户2
4|AMZ合作账户3
3|德国
2|日本
10|AMZ备用账号*/
		//日本店铺
		
		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country =1 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_amazon1 = $resShopid["id"];
		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 6 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_amazon2 = $resShopid["id"];
		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 7 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_amazon3 = $resShopid["id"];
		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 8 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_amazon4 = $resShopid["id"];
		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 9 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_amazon5 = $resShopid["id"];


		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 2 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_jp = $resShopid["id"];

		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 3 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_de = $resShopid["id"];

		$resShopid = $this->fun->getdatabasesdetail("d_shop"," AND  country = 10 and platfrom = 2","GROUP_CONCAT(id) as  id");
		$str_usaa = $resShopid["id"];
	

		$field = " SUM($salenum) as allsale,goods_parent_sku,goods_name,SUM(sales2) as sales2,SUM(sales1) as sales1,goods_parentID,GROUP_CONCAT(goods_id) as goods_id,saleNum";
		$orderby =" group by goods_parentID  having allsale>=$nums order by allsale desc";

		$list = $this->fun->getalllist('db_goods  ',$where,$pages,$if_csv,100,$orderby,$field);
		
		foreach($list["list"] as $key=>$rs)
		{
			$platform = $this->getsaleplatform($rs["goods_id"],$type);
			if(count($platform)==0)
			{
				$list["list"][$key]["noplatform"] = 1;
			}else{
				$list["list"][$key]["noplatform"] = 0;
				//只统计亚马逊 德国、日本 、其他
				foreach($platform as $k=>$v){
					if($v['name']=="Amazon"){
						//

						//统计德国  店铺id
						$platform[$k]['germany'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_de}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						
						//$results = $this->m->getsaleplatform($rs["goods_id"],$str_de);
						if($results){
							$platform[$k]['germany'] = $results["goods_number"];
						}

						//统计日本 店铺id
						$platform[$k]['japan'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_jp}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['japan'] = $results["goods_number"];
						}
						
						
						//统计亚马逊1
						$platform[$k]['str_amazon1'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_amazon1}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['str_amazon1'] = $results["goods_number"]?$results["goods_number"]:0;
						}

						//统计亚马逊2
						$platform[$k]['str_amazon2'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_amazon2}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['str_amazon2'] = $results["goods_number"]?$results["goods_number"]:0;
						}

						//统计亚马逊3
						$platform[$k]['str_amazon3'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_amazon3}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['str_amazon3'] = $results["goods_number"]?$results["goods_number"]:0;
						}

						//统计亚马逊4
						$platform[$k]['str_amazon4'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_amazon4}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['str_amazon4'] = $results["goods_number"]?$results["goods_number"]:0;
						}

						//统计亚马逊4
						$platform[$k]['str_amazon5'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_amazon5}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['str_amazon5'] = $results["goods_number"]?$results["goods_number"]:0;
						}

						//统计亚马逊备用
						$platform[$k]['str_usaa'] = 0;
						$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in({$rs['goods_id']}) and B.shopid in({$str_usaa}) AND B.order_status in(4,5) GROUP by B.platformID";
						$results = $this->db->query($sql)->row_array();
						if($results){
							$platform[$k]['str_usaa'] = $results["goods_number"]?$results["goods_number"]:0;
						}


						//统计其他
						$platform[$k]['other'] = $v['goods_number']-$platform[$k]['japan']-$platform[$k]['germany']-$platform[$k]['str_amazon1']-$platform[$k]['str_amazon2']-$platform[$k]['str_amazon3']-$platform[$k]['str_amazon4']-$platform[$k]['str_amazon5']-$platform[$k]['str_usaa'];
						if($platform[$k]['other']<0){
							$platform[$k]['other'] = 0;
						}
					}

				}



				$list["list"][$key]["platform"] = $platform;

			
			}
			if($rs["sales1"]==0)
			{
				$list["list"][$key]["sales1"] = "";
			}
			if($rs["sales2"]==0)
			{
				$list["list"][$key]["sales2"] = "";
			}
			
			//print_r($list);
			 $rsdb = $this->fun->getdatabasesdetail("db_goods_recommend" ," AND parent_goods_id = '".$rs["goods_parentID"]."' AND is_stop=0");
			 if($rsdb['receivers']){
			 $receivers = trim($rsdb['receivers'],",");
					$receivers = explode(",",$receivers);
					//返回相关信息
					$return_reason = explode(",",$rsdb['return_reason']);
					$arr_msg = array();
					foreach($receivers as $k=>$v){
						$arr_msg[$k]['userid'] = $v;
						$arr_msg[$k]['flag']=0;
						$arr_msg[$k]['address']=0;
						foreach($return_reason as $kk=>$vv){
							$arr_reason = explode("-",$vv);

							if($v==$arr_reason[0]){
								$arr_msg[$k]['flag']=$arr_reason[1];
								$str_con = $arr_reason[0]."-".$arr_reason[1]."-";
								$arr_msg[$k]['address']=str_replace($str_con,"",$vv);
								break;
							}
						}
					}
				 $list["list"][$key]["receivers"] = $arr_msg; //所有有返回消息的接受者
				 $list["list"][$key]["status"] = $rsdb['status'];
				 $list["list"][$key]["linkUrl"] = $rsdb['linkUrl'];
			 }
           
		}

		return $list;
	}
	function getsaleplatform($goods_id,$type=0)
	{
		if($type==0){
			$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in($goods_id) AND B.order_status in(4,5) and B.platformID !=5 and B.platformID !=6 and B.platformID !=21 GROUP by B.platformID";
		}else{
			$sql = "SELECT B.platformID,C.name,A.goods_sn,SUM(A.goods_number) as goods_number FROM `db_order_sell` A LEFT JOIN db_order_list B ON A.order_id = B.order_id LEFT JOIN db_platform C on B.platformID = C.id  WHERE goods_id in($goods_id) AND B.order_status in(4,5)  GROUP by B.platformID";
		}
	    $query = $this->db->query($sql);
        $dates = $query->result_array();
		return $dates;
	}
	function savesaleproduct()
	{
		$postdata["sku"] = trim($this->input->post('sku'));
		$postdata["name"] = trim($this->input->post('name'));
		$postdata["costprice"] = trim($this->input->post('costprice'));
		$postdata["price"] =trim($this->input->post('price'))?trim($this->input->post('price')):0;
		$postdata["activityprice"] = trim($this->input->post('activityprice'))?trim($this->input->post('activityprice')):0;
		$postdata["clearanceprice"] =trim($this->input->post('clearanceprice'))?trim($this->input->post('clearanceprice')):0;
		$postdata["type"] = 1;
		$postdata["tb"] = 1;
		$postdata["tbopertime"] =trim($this->input->post('tbopertime'));
		$liquidation = date("Y-m-d", strtotime("+2 months", strtotime(trim($this->input->post('tbopertime')))));
		$postdata["liquidation"] =$liquidation;
		 $zhixiaotime = date("Y-m-d", strtotime("+6 months", strtotime(trim($this->input->post('tbopertime')))));
		$postdata["zhixiaotime"] =$zhixiaotime;
		$postdata["status"] =1;
		$postdata["account"] =$_COOKIE["useraccount"];
		$postdata["createUser"] =$_COOKIE["username"];
		$postdata["createID"] =$_COOKIE["userid"];
		$postdata["weburl"] = trim($this->input->post('weburl'));
		$postdata["opertime"] =date("Y-m-d H:i:s");



		//获取提成发放月
		$moneydata = $this->getmoneydata(trim($this->input->post('tbopertime')),1);
		$postdata["money_year"] =date("Y",strtotime($moneydata));
		$postdata["money_month"] =date("m",strtotime($moneydata));
		
		$id = $this->input->post('id');	
		if($id)
		{
			$this->db->update('p_product', $postdata, 'id=' . $id);
			
		}else{
			$this->db->query($this->db->insert_string('p_product', $postdata));
			$id = $this->db->insert_id();
		}
		
		return $id;
		
	
	}
	function saveproduct()
	{

		$id = $this->input->post('id');
		if($id)
		{
			$sql="UPDATE p_product SET ";
		}else{
			$sql="INSERT INTO p_product SET ";
		}
		$sql.=" sku='".trim($this->input->post('sku'))."'";
		$sql.=" ,type='0'";
		$sql.=" ,name='".trim($this->input->post('name'))."'";
		$sql.=" ,costprice='".trim($this->input->post('costprice'))."'";
		$sql.=" ,price='".trim($this->input->post('price'))."'";
		$sql.=" ,activityprice='".trim($this->input->post('activityprice'))."'";
		$sql.=" ,clearanceprice='".trim($this->input->post('clearanceprice'))."'";
		$sql.=" ,weburl='".trim($this->input->post('weburl'))."'";
		
		if(trim($this->input->post('tbopertime')))
		{
			$startime1 = date("Y-m-d", strtotime("+3 months", strtotime($this->input->post('tbopertime'))));
			$zhixiaotime = date("Y-m-d", strtotime("+6 months", strtotime($this->input->post('tbopertime'))));
			$sql.=" ,tbopertime='".$this->input->post('tbopertime')."'";
			$sql.=" ,liquidation='".$startime1."'";
			$sql.=" ,zhixiaotime='".$zhixiaotime."'";
			$sql.=" ,tb=1";
		}else{
			//自动更新马帮入库时间
			$this->load->library('Mabang');
			$res = $this->mabang->updatelocalhostproduct(trim($this->input->post('sku')));
			if($res)
			{
				$sql.=" ,tbopertime='".$res["tbopertime"]."'";
				$sql.=" ,liquidation='".$res["liquidation"]."'";
				$sql.=" ,zhixiaotime='".$res["zhixiaotime"]."'";
				$sql.=" ,tb=1";
			}else{
				
				$sql.=" ,tb=0";
			}
		}

		if($id)
		{
			$sql.=" WHERE id ='".$id."' ";
			$this->db->query($sql);
			
		}else{
			$sql.=" ,status='1'";
			$sql.=" ,account='".$_COOKIE["useraccount"]."'";
			$sql.=" ,createUser='".$_COOKIE["username"]."'";
			$sql.=" ,createID='".$_COOKIE["userid"]."'";
			$sql.=" ,opertime='".date("Y-m-d H:i:s")."'";
			$this->db->query($sql);
			$id = $this->db->insert_id();
		}
		
		return $id;
	}

	function delproduct()
	{
		$id = $this->input->post('id');
		$this->db->query("DELETE FROM p_product WHERE id = '".$id."'");
	}
	
	/*************
	** type  1 按销售额计算
	** tbopertime 入库日
	***/
	function getmoneydata($tbopertime,$type)
	{
		if($type==1)
		{
			$liquidation = date("Y-m-d", strtotime("+89 day", strtotime($tbopertime)));
			
			$month = date("m",strtotime($liquidation));
			$day = date("d",strtotime($liquidation));
			$q = ceil($month/3);
			switch($q)
			{
				case 1;
				$months = date("Y",strtotime($liquidation))."-03";
				break;
				case 2;
				$months = date("Y",strtotime($liquidation))."-06";
				break;
				case 3;
				$months = date("Y",strtotime($liquidation))."-09";
				break;
				case 4;
				$months = date("Y",strtotime($liquidation))."-12";
				break;

			}
			return $months;	
		}
	}
	
	function selectShop($where)
	{
		$depts = $this->fun->getdatabaseslist("d_shop"," and del_flag =0 $where","id,name,platfromname,updateoper,otherUpload,country");
        foreach ($depts as $key => $rs) {
            $deptsname[$rs["platfromname"]][] = $rs;
        }
		return $deptsname;
	}
	public function getDsViewShops(){
        $authname=$_COOKIE['username'];
        $viewshopname = $_COOKIE["shopname"];	//开通了权限的人员需要绑定店铺
        if($viewshopname){
        	$viewshopname = unserialize($viewshopname);
            if(!is_array($viewshopname)){$viewshopname=array();}
            if(in_array('all',$viewshopname)){
                $lookshops='all';
            }
        }       
        if($_COOKIE["department"] ==1 || $_COOKIE["power"]=='all' || $_COOKIE["department"]=='5' || $lookshops=='all'){//管理者能看到所有店铺
			$where = "";
            return 'isadmin';
		}else{
		    $viewsshops=array();
            $viewshopname = $_COOKIE["shopname"];	//开通了权限的人员需要绑定店铺
			if($viewshopname){
				$viewshopname = unserialize($viewshopname);
				if($viewshopname){
					foreach($viewshopname as $key=>$name){
                        $viewsshops[]=$name;
					}
				}
			}
            
            
            $where=" and (updateoper='".$authname."' or otherUpload like '%$authname%')";
            $shopname  = $this->selectShop($where);
            
            if(count($shopname)>0){
                foreach($shopname as $key=>$platlist){
                    foreach($platlist as $index=>$val){
                        $val['otherUpload']=explode(',',$val['otherUpload']);
                        if($val['updateoper']!=$authname and !in_array($authname,$val['otherUpload'])){
                            unset($shopname[$key][$index]);
                        }else{
                            $viewsshops[]=$val['name'];
                        }
                    }
                }
                
            }
            $viewshop=implode(",",$viewsshops);
            return $viewshop;
		}
    }
	function getordercosprice($ordersn)
	{
		$orderlist = $this->fun->getdatabaseslist("db_order_sell A LEFT JOIN db_order_list B on A.order_id =B.order_id"," AND B.order_sn='".$ordersn."'"," goods_id,goods_number,goods_sn ");
		$allcosprice = 0;
		foreach($orderlist as $key=>$goods)
		{
			$cosprice = $this->fun->get_Goods_costprice($goods["goods_sn"]);//获取商品成本
			$allcospricest = $cosprice*$goods["goods_number"];
			$allcosprice = $allcosprice+$allcospricest;
		}
		return $allcosprice;
		
	}
	function getallscanorder()
	{
		$orderlist = $this->db->get_where("db_order_batchexpress"," yz= 0")->result_array();
		if(count($orderlist)>0)
		{
			foreach($orderlist as $key=>$rs)
			{
				$resorder = $this->db->get_where("db_mabang_scanorder"," batch_order_sn='" .trim($rs["batch_order_sn"]). "' ")->result_array();
				if(count($resorder)>=1)
				{
					$this->db->update("db_order_batchexpress",array("yz"=>1),"id =".$rs["id"]);
					continue;
				}

				$orderinfo = $this->fun->getdatabasesdetail("db_order_list"," AND order_sn='".trim($rs["batch_order_sn"])."'");
				
				 /*商品信息*/
				 $allcostprice =$totalgoods=0;
				 $skulist="";
				$goodslist = $this->fun->getdatabaseslist("db_order_sell A left join db_goods B ON A.goods_sn=B.goods_sku", " AND order_id='" . $orderinfo['order_id'] . "'");
				if (count($goodslist) > 0) {
					foreach ($goodslist as $key => $rsgoods) {
						$acostprice = 0;
						//由于之前成本不准，这里重新计算成本
						//$cos = $this->fun->get_Goods_costprice($rsgoods['goods_sn']);
						//$costprice = $cos;
						
						//使用订单的成本
						$costprice = $rsgoods["costprice"];
						if(!$costprice)
						{
							//重新获取成本
							$cos = $this->fun->get_Goods_costprice($rsgoods['goods_sn']);
							$costprice = $cos;
						}
						$acostprice = $costprice * $rsgoods["goods_number"];
						$allcostprice = $allcostprice + $acostprice;
						$totalgoods = $totalgoods + $rsgoods["goods_number"];
						$skulist.= $rsgoods["goods_sku"].",";

					}
				}


				if($orderinfo['shipping_realfee']>0)
				{
					$shipping_realfee = $orderinfo['shipping_realfee'];
				}else{
					$shipping_realfee = $orderinfo['shipping_expectfee'];
				}
				$post="";
				$post["batch_order_sn"] = $rs["batch_order_sn"];
				$post["batch_shipping_number"] = $rs["batch_shipping_number"];
				$post["batch_shipping_name"] = $rs["batch_shipping_name"];
				$post["batch_shipping_id"] = $rs['batch_shipping_id'];
				$post["batch_orderweight"] = $rs['batch_orderweight'];//扫描重量
				$post['mabangorderTime']  = $orderinfo['ordertime'];//订单时间
				$post['oper'] = $rs['oper'];
				$post['opertime'] = $rs['opertime'];
				$post['yz'] = 1;
				$post['SHOPTYPE'] = $orderinfo['shopname'];
				$post['SHOPTYPEID'] =  $orderinfo['shopid'];
				$post['MONEYASK'] =  $orderinfo['goods_amount'];	//收款金额
				$post['MONEYACTION']  = $allcostprice;	//商品总成本
				$post['MONEYEXPRESSASK'] = $orderinfo['shipping_fee'];	//收取运费
				$post['PAYPALFEEAMOUNT'] = $orderinfo['pay_fee'];		//Paypal费用
				$post['SELLNUM'] = $totalgoods;
				$post['FINALVALUEFEE'] = $orderinfo['platform_fee'];
				$post['QLRESERVE2'] = $rs['skulist']?$rs['skulist']:$skulist;
				$post['cn_country'] = $orderinfo['country_cn'];
				$post['country'] = $orderinfo['country'];
				$post['freight'] = $shipping_realfee;//预估运费
				$post["isform"] = 2;

				$maoli =$orderinfo['goods_amount']+$orderinfo['shipping_fee'] - $allcostprice-$orderinfo['pay_fee']-$orderinfo['platform_fee']-$shipping_realfee;
				$post["maoli"] = $maoli;
				$allmoney =$orderinfo['goods_amount']+ $orderinfo['shipping_fee'];
				if($allmoney>0)
				{
					$post["maolilv"] = round($maoli / ($allmoney),3)*100;
				}else{
					$post["maolilv"] = 0;
				}
			
				$this->db->insert("db_mabang_scanorder",$post);

				$this->db->update("db_order_batchexpress",array("yz"=>1),"id =".$rs["id"]);
				

			}
		
		}
	}

	public function getsalessum($sku, $sumday, $tbopertime)
    {
        $times = $tbopertime;

        $endtime = date("Y-m-d", strtotime("+$sumday months", strtotime($tbopertime)));
        $startime = $tbopertime;
		
		$sql="SELECT  sum(A.goods_number) as goods_number  from db_order_sell  A left JOIN db_order_list B on A.order_id = B.order_id where A.goods_sn='".$sku."' AND ( B.order_status=5) and B.dates >='".$startime."' and B.dates <='".$endtime."'";
		$res = $this->db->get_one($sql);
		if($res['goods_number'])
		{
			return $res['goods_number'];
		}else{
			return 0;
		}
    }

	public function salecommission($tbopertime,$sku,$liquidation,$costprice)	//按销售额统计提成
	{
		$allnums = 0;
		$maolvshouru = 0;
        $minvalue = array();
        $amount = 0;
		$endtime = $liquidation . " 23:59:59";
        $startime = $tbopertime . " 00:00:00";
		$qsorder = $this->getsaleslastsum($startime, $endtime, $sku);
		foreach($qsorder as $key=>$rs)
		{
			 $allnums = $allnums + $rs["goods_number"];	//总销量
			if($rs['shipping_realfee']>0)
			{
				$MONEYEXPRESSASKst = round($rs['shipping_realfee'],2);//真实运费
			}else{
				$MONEYEXPRESSASKst = round($rs['shipping_expectfee'],2);//预估运费
			}

			if($MONEYEXPRESSASKst<1)
			{
				//如果没有运费，自动计算10笔运费平均值
				$getyunf = $this->getsalembyunf($sku,$startime,$endtime,$rs["country"]);
				$MONEYEXPRESSASKst = $getyunf;	//计算十笔平均值运费
			}
			
			if($MONEYEXPRESSASKst>1){

				//获取一个订单的SKU数，如果大于1，说明客人买了多个
				$goodslist = $this->fun->getdatabaseslist("db_order_sell", " AND order_id='" . $rs['order_id'] . "'");
				if(count($goodslist)>1)	//2个以上商品
				{
					$smoney =$rs['goods_amount'];//收款金额
					if($rs["shipping_fee"]>0)
					{
						$expressask = round($rs["shipping_fee"]/count($goodslist),2) ;	//运费收入
					}else{
						$expressask  = 0;
					}
				    $cosexpress = round($MONEYEXPRESSASKst/count($goodslist),2) ;	//运费成本
				}else{
					$smoney = $rs['goods_amount'];	//收款金额
					$expressask  =$rs["shipping_fee"];//运费收入
					$cosexpress  =$MONEYEXPRESSASKst;//运费成本
					
				}
				$jiaoyif = $rs['platform_fee'];
				$fukuanf =  $rs['pay_fee'];
				$totalcostprice = $totalcostprice + ($costprice*$rs['goods_number']);
				$totalsmoney = $totalsmoney+$smoney;	
				$totalexpressask = $totalexpressask+$expressask;
				$totalcosexpress = $totalcosexpress+$cosexpress;
				$totaljiaoyif = $totaljiaoyif+$jiaoyif;
				$totalfukuanf = $totalfukuanf+$fukuanf;
			}

		

			 

		}

			$amount =round( ($totalsmoney+$totalexpressask-$totalcostprice-$totalcosexpress-$totaljiaoyif-$totalfukuanf)*0.1,2);
			
			$shuru = $totalsmoney+$totalexpressask;
			
			if($shuru>0){
				$maolvshouru =($amount/0.1)/$shuru* 100;
			}
		 return array("amount"=>$amount,"maolvshouru"=>$maolvshouru,"totalsmoney"=>$totalsmoney,"totalexpressask"=>$totalexpressask,"totalcostprice"=>$totalcostprice,"totalcosexpress"=>$totalcosexpress,"totaljiaoyif"=>$totaljiaoyif,"totalfukuanf"=>$totalfukuanf);
	}

	 /***************************
     ** 查询提成金额
     ** tbopertime 入库时间
     *********************************/
    public function commission($tbopertime, $sku, $costprice, $activityprice)	//按销售数量统计提成
    {

        $allnums = 0;
        $minvalue = array();
        $amount = 0;


        $startime1 = date("Y-m-d", strtotime("+3 months", strtotime($tbopertime)));
        $endtime = $startime1 . " 23:59:59";
        $startime = $tbopertime . " 00:00:00";


        $qsorder = $this->getsaleslastsum($startime, $endtime, $sku);
		
	
		$maoli = 0;
		foreach($qsorder as $key=>$rs)
		{
			 $allnums =$rs["goods_number"];	//总销量
			if($rs['shipping_realfee']>0)
			{
				$MONEYEXPRESSASKst = round($rs['shipping_realfee'],2);//真实运费
			}else{
				$MONEYEXPRESSASKst = round($rs['shipping_expectfee'],2);//预估运费
			}

			if($MONEYEXPRESSASKst<1)
			{
				//如果没有运费，自动计算10笔运费平均值
				$getyunf = $this->getsalembyunf($sku,$startime,$endtime,$rs["country"]);
				$MONEYEXPRESSASKst = $getyunf;	//计算十笔平均值运费
			}
			
			if($MONEYEXPRESSASKst>1){

				//获取一个订单的SKU数，如果大于1，说明客人买了多个
				$goodslist = $this->fun->getdatabaseslist("db_order_sell", " AND order_id='" . $rs['order_id'] . "'");
				if(count($goodslist)>1)	//2个以上商品
				{
					$smoney =$rs['goods_amount'];//收款金额
					if($rs["shipping_fee"]>0)
					{
						$expressask = round($rs["shipping_fee"]/count($goodslist),2) ;	//运费收入
					}else{
						$expressask  = 0;
					}
				    $cosexpress = round($MONEYEXPRESSASKst/count($goodslist),2) ;	//运费成本
				}else{
					$smoney = $rs['goods_amount'];	//收款金额
					$expressask  =$rs["shipping_fee"];//运费收入
					$cosexpress  =$MONEYEXPRESSASKst;//运费成本
					
				}
				$jiaoyif = $rs['platform_fee'];
				$fukuanf =  $rs['pay_fee'];
				
				
				$maoli = $smoney + $expressask - $cosexpress - $jiaoyif - $fukuanf - $costprice;

				$amount = $amount+( $maoli * 20 / 100);
			
			}

		

			 

		}




//        foreach ($qsorder as $key => $rs) {
//            $allnums = $allnums + $rs['goods_number'];
//        }
//
//
//        if ($activityprice > 0) {
//            if ($allnums > 0) {
//               $amount = $allnums * ($activityprice - $costprice) * 20 / 100;
//            }
//        }

        return round($amount,2);
    }
	/******
	*** 计算10笔平均运费
	***	**********/
	public function getsalembyunf($sku,$stime,$endtime,$country)
	{
		$times = date("Y-m-d H:i:s");
		$rsdb = $this->db->get_one("SELECT * FROM db_mabangsalebonus WHERE sku='".$sku."' AND  DATE_SUB(instime,  INTERVAL 60 DAY) <= '".$times."'");
		if($rsdb)
		{
			return $rsdb["moneyexpressaction"];
		}
		$sumf=0;
		$sql = "SELECT * from db_mabang_scanorder where country ='".$country."' and SELLNUM=1 and QLRESERVE2 LIKE '".$sku."'  and freight >0  order by id DESC LIMIT 0 ,10 ";
		$query = $this->db->query($sql);
		$res = $query->result_array();
	
		if(count($res)==0)
		{
			$yunfei = 0;
		}else{
			if(count($res)>1)
			{
				foreach($res as $key=>$rs)
				{
					$sumf = $sumf+$rs['freight'];
				}
				$yunfei = $sumf/count($res);
				
			}else{
				$yunfei = 0;
			}
		}
		if($yunfei>0){
			$rsdb = $this->db->get_one("SELECT * FROM db_mabangsalebonus WHERE sku='".$sku."'");
			$postty="";
			$postty["moneyexpressaction"] = $yunfei;
			$postty["sku"] = $sku;
			$postty["ty"] =1;
			$postty["instime"] = date("Y-m-d H:i:s");
			
			if($rsdb)
			{
				$this->db->update("db_mabangsalebonus",$postty," id='".$rsdb["id"]."'");
			}else{
				$this->db->insert("db_mabangsalebonus",$postty);
			}
		}
		return $yunfei;
	}
	 /***************************
     ** 查询三个月的清算销售
     ** tbopertime 入库时间
     *********************************/
    public function getsaleslastsum($tbopertime, $endtime, $sku)
    {
		 //$sql = "SELECT A.ORDERNUM as ORDERNUMLAST ,A.AMOUNT,A.ORDERNUM,B.MONEYEXPRESSASK,B.ORDERID,B.MONEYASK,B.CUSTOMERTYPE,B.OPERTIME,B.STATUS,B.SELLNUM AS SELLNUMs,B.CUSTOMERCOUNTRY,B.MONEYEXPRESSACTION as MONEYEXPRESSACTIONS,B.ORDERTIME FROM DB_SELL A left join DB_ORDER B on A.ORDERID = B.ORDERID where A.PRODUCTID='" . $sku . "' AND B.STATUS ='已完成'  AND to_char(B.ORDERTIME,'yyyy-mm-dd hh24:mi:ss')>='" . $tbopertime . "' and to_char(B.ORDERTIME,'yyyy-mm-dd hh24:mi:ss')<='" . $endtime . "' AND  A.FLAG !=3";

		$sql="SELECT A.*,B.shipping_fee,B.shipping_realfee,B.shipping_expectfee,B.shopname,B.order_sn,B.dates,B.country,B.order_id,B.platform_fee,B.pay_fee FROM db_order_sell A LEFT JOIN db_order_list B on A.order_id = B.order_id where A.goods_sn='".$sku."' AND B.order_status = 5 and B.ordertime >='".$tbopertime."' and B.ordertime <='".$endtime."' AND (B.order_source ='正常订单' or B.order_source ='手工创建') ";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		//print_r($dates);
        return $dates;

    }

	public function getstockcleardataList($goods_id,$time1,$type)
	{
		$allprofits = 0; //利润
		$allmoney = 0;
		$lastMoney = 0;
		$allshourumoney =$alllastMoney= 0;
		$list = array();
		$return = 0;
		$where= "AND (order_source='正常订单' or order_source='手工创建' or order_source='合并订单' )";
		$where.=" AND shopname !='赞助'";
		$sql = "SELECT order_id,order_sn,order_status,order_source,country_cn,shopname,moneyrate,moneytype,tradeid,shipping_realfee,shipping_expectfee,actionorderweight,goods_originask,goods_amount,shipping_fee,shipping_originask,pay_fee,platform_fee,shipping_time,ordertime,saleschannel,orderweight,platformID,dates FROM db_order_list where (order_status = 5 or order_status = 4) $where and shipping_time >='".strtotime($time1)."'  and order_id in(select distinct order_id from db_order_sell where goods_id in ($goods_id)) order by order_id DESC";
		$query = $this->db->query($sql);
		$dates = $query->result_array();

		if(count($dates)>0)
		{
			foreach($dates as $key=>$order)
			{
				$sell = $this->fun->getdatabaseslist("db_order_sell"," AND order_id ='".$order["order_id"]."'");
				if(count($sell) == 1)
				{
					if($order['shipping_realfee']>0)
					{
						$shipping_realfee = $order['shipping_realfee'];
					}else{
						$shipping_realfee =  $order['shipping_expectfee'];
					}
					if($sell[0]['costprice'])
					{
						$costprice = $sell[0]['costprice']*$sell[0]['goods_number'];
					}else{
						$goods = $this->fun->getdatabasesdetail("db_goods"," AND goods_id='".$sell[0]['goods_id']."'");
						$costprice = $goods['g_costprice']*$sell[0]['goods_number'];
					}
					$shourumoney = $order['goods_amount'] + $order['shipping_fee'];
					$allmoney = ($order['goods_amount'] + $order['shipping_fee'])  - $order['platform_fee'] - $order['pay_fee'] - $shipping_realfee - $costprice;
					$dates[$key]["costprice"] = $costprice;
					$dates[$key]["goods_amount"] =$order['goods_amount'];
				}else{
					
					//超过1个，所有的金额都平摊
					$allmoneyData = $this->getmoreselllist($order,$goods_id,count($sell));
					
					$allmoney = $allmoneyData["allmoney"];
					$dates[$key]["costprice"] = $allmoneyData["costprice"];
					$dates[$key]["goods_amount"] =$allmoneyData['goods_amount'];
					$dates[$key]["shipping_fee"] =$allmoneyData['allshipping_fee'];
					$dates[$key]["platform_fee"] =$allmoneyData['allplatform_fee'];
					$dates[$key]["shipping_realfee"] =$allmoneyData['allshipping_realfee'];
					$shourumoney = $allmoneyData['goods_amount'] + $allmoneyData['shipping_fee'];
					
				}
				if($allmoney >0)
				{
					
						//如果是维护的商品，进来就只有1%
						$dates[$key]["isoff"] = 1;//90天外
						$lastMoney = $allmoney * 0.01;
				}
				if($allmoney <0)
				{
					//小于0，说明订单负数,负数公司和个人各一半
					if($xh==1)
					{
						$dates[$key]["isoff"] = 1;//90天外
					}else{
						$dates[$key]["isoff"] = 0;//90天内
					}
					$lastMoney = 0;
				}
				$dates[$key]["lastMoney"] = round($lastMoney,2);
				$dates[$key]["allmoney"] = round($allmoney,2);
				$dates[$key]["shipping_time"] = date("Y-m-d",$order['shipping_time']);
				$dates[$key]["iscome"] =1;	//直发订单

				//如果计算时间在90天内以10%计算，如果计算时间在90天外以1%计算。需要根据订单来计算

				//查询FBA的相关订单与利润报表

				 $allprofits = $allprofits+$allmoney;//所有利润
				 $alllastMoney = $alllastMoney+$lastMoney;//利润比
				  $allshourumoney = $allshourumoney+$shourumoney;//总收入金额
			}
		

		}

	
		if($type==0){
			return array("allprofits"=>round($allprofits,2),"alllastMoney"=>round($alllastMoney,2),"allshourumoney"=>round($allshourumoney,2));
		}else{
			return  array("dates"=>$dates,"allprofits"=>round($allprofits,2),"alllastMoney"=>round($alllastMoney,2),"allshourumoney"=>round($allshourumoney,2));
		}

		//如果有订单，计算利润
	}
	
	/***************
	** 获取一段天数内SKU的销售 
	**  time1 上次结算时间【如果没有结算时间，以上架时间为准】
	**  time2 一年后的时间【订单计算上次结算到一年后时间内的所有订单】
	**  onefishTime 90天时间段
	**  type 0 显示最后统计，1 显示明细
	**   xh 0 开发和维护  1 维护人员按1%
	**   FBA和直发合计
	*********/
	public function getexternalBuySell($sku,$time1,$time2,$onefishTime,$TwofishTime,$type=0,$xh=0,$order_id=0,$amazonT=0)
	{
		$allprofits = 0; //利润
		$allmoney = 0;
		$lastMoney = 0;
		$list = array();
		$return = 0;
		//$time1 = "2017-10-01 00:00:00";
		
		if($order_id)
		{
			if($amazonT==0){
				$where=" and order_id = ".intval($order_id);
				$whereamazon="";
			}else{
				$whereamazon=" and amazon_id = ".intval($order_id);
			}
		}else{
			$where= "AND (order_source='正常订单' or order_source='手工创建' or order_source='合并订单' or order_source='拆分订单' )";
		}
		$where.=" AND shopname !='赞助'";
		$goods_parent = $this->db->get_one("SELECT GROUP_CONCAT(goods_id) as goods_id from db_goods where goods_parent_sku LIKE '".$sku."'");
		$goods_id = $goods_parent["goods_id"];
		if(!$goods_id)
		{
			return;
		}
		$sql = "SELECT order_id,order_sn,order_status,order_source,country_cn,shopname,moneyrate,moneytype,tradeid,shipping_realfee,shipping_expectfee,actionorderweight,goods_originask,goods_amount,shipping_fee,shipping_originask,pay_fee,platform_fee,shipping_time,ordertime,saleschannel,orderweight,platformID,dates FROM db_order_list where (order_status = 5 or order_status = 4) $where and shipping_time >='".strtotime($time1)."' AND shipping_time <='".strtotime($time2)."' and order_id in(select distinct order_id from db_order_sell where goods_id in ($goods_id)) order by order_id DESC";
		
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		//如果有订单，计算利润
		if(count($dates)>0)
		{
			foreach($dates as $key=>$order)
			{
				$sell = $this->fun->getdatabaseslist("db_order_sell"," AND order_id ='".$order["order_id"]."'");
				if(count($sell) == 1)
				{
					if($order['shipping_realfee']>0)
					{
						$shipping_realfee = $order['shipping_realfee'];
					}else{
						$shipping_realfee =  $order['shipping_expectfee'];
					}
					if($sell[0]['costprice'])
					{
						$costprice = $sell[0]['costprice']*$sell[0]['goods_number'];
					}else{
						$goods = $this->fun->getdatabasesdetail("db_goods"," AND goods_id='".$sell[0]['goods_id']."'");
						$costprice = $goods['g_costprice']*$sell[0]['goods_number'];
					}
					$allmoney = ($order['goods_amount'] + $order['shipping_fee'])  - $order['platform_fee'] - $order['pay_fee'] - $shipping_realfee - $costprice;
					$dates[$key]["costprice"] = $costprice;
					$dates[$key]["goods_amount"] =$order['goods_amount'];
				}else{
					//超过1个，所有的金额都平摊
					$allmoneyData = $this->getmoreselllist($order,$goods_id,count($sell));
					
					$allmoney = $allmoneyData["allmoney"];
					$dates[$key]["costprice"] = $allmoneyData["costprice"];
					$dates[$key]["goods_amount"] =$allmoneyData['goods_amount'];
					$dates[$key]["shipping_fee"] =$allmoneyData['allshipping_fee'];
					$dates[$key]["platform_fee"] =$allmoneyData['allplatform_fee'];
					$dates[$key]["shipping_realfee"] =$allmoneyData['allshipping_realfee'];
					
					
				}
				if($allmoney >0)
				{
					if($xh==1)
					{
						//如果是维护的商品，进来就只有1%
						$dates[$key]["isoff"] = 1;//90天外
						$lastMoney = $allmoney * 0.01;
					}else{
						if($order["shipping_time"]<=$onefishTime)	//90天内10%毛利
						{
							$dates[$key]["isoff"] = 0;//90天内
							$lastMoney = $allmoney * 0.1;
						}else{
							$dates[$key]["isoff"] = 1;//90天外
							$lastMoney = $allmoney * 0.01;
						}
					}
				}
				if($allmoney <0)
				{
					//小于0，说明订单负数,负数公司和个人各一半
					if($xh==1)
					{
						$dates[$key]["isoff"] = 1;//90天外
					}else{
						$dates[$key]["isoff"] = 0;//90天内
					}
					$lastMoney = $allmoney /2;
				}
				$dates[$key]["lastMoney"] = round($lastMoney,2);
				$dates[$key]["allmoney"] = round($allmoney,2);
				$dates[$key]["shipping_time"] = date("Y-m-d",$order['shipping_time']);
				$dates[$key]["iscome"] =1;	//直发订单

				//如果计算时间在90天内以10%计算，如果计算时间在90天外以1%计算。需要根据订单来计算

				//查询FBA的相关订单与利润报表

				 $allprofits = $allprofits+$allmoney;//所有利润
				 $alllastMoney = $alllastMoney+$lastMoney;//利润比
			}
		
			
			$return = 1;

		}
		
		
		//读取FBA的订单报表。如果有数据与原来的合并
	
		$sql = "SELECT * FROM db_amzordertmp where (status = 1) and instime >='".strtotime($time1)."' AND instime <='".strtotime($time2)."' $whereamazon and id in(select distinct ordertmpid from db_amzordertmpsell where goods_id in ($goods_id)) order by id DESC";
	
		$query = $this->db->query($sql);
		$FBAdates = $query->result_array();
		$allmoney =$lastMoney=$FBAallprofits=$FABalllastMoney=0;
		$Newi = count($dates)+1;
		
	
		if(count($FBAdates)>0)
		{
			foreach($FBAdates as $key=>$FBAorder)
			{
				
				$dates[$Newi]["order_id"] = $FBAorder["id"];
				$dates[$Newi]["order_sn"] = $FBAorder["tradeid"];
				
				$dates[$Newi]["shipping_fee"] = $FBAorder['shipping_fee'];
				$dates[$Newi]["platform_fee"] = $FBAorder['payship_fee'];

				$dates[$Newi]["pay_fee"] = 0;
				$dates[$Newi]["shopname"] = $FBAorder['shopname'];
				

				
				$sell = $this->fun->getdatabaseslist("db_amzordertmpsell"," AND ordertmpid ='".$FBAorder["id"]."'");
				if(count($sell) == 1)
				{
					
					$shipping_realfee = 0;
					//FBA的利润 = 商品收入+运费收入 - 商品成本 - FBA费用  - 头程
					if($sell[0]['deliverprice']>0)
					{
						
						$shipping_realfee = $sell[0]['deliverprice']*$sell[0]['ordernum'];//头程
					}else{
						//如果没有头程，重新去计算
						$derprice = $this->fun->get_oversea_express_costprice(trim($sell[0]['productid']),$FBAorder['shopname']);
						if($derprice>0){
							$shipping_realfee = $derprice*$sell[0]['ordernum'];//头程
						}else{
							$shipping_realfee = 0;
						}
					}
					
					if($sell[0]['costprice'])
					{
						$costprice = $sell[0]['costprice'];
					}else{
						$goods = $this->fun->getdatabasesdetail("db_goods"," AND goods_id='".$sell[0]['goods_id']."'");
						$costprice = $goods['g_costprice'];
					}

					$allmoney = ($FBAorder['goods_amount'] + $FBAorder['shipping_fee'])  - $shipping_realfee - $costprice + $FBAorder['payship_fee'];
					$dates[$Newi]["costprice"] = $costprice;
					$dates[$Newi]["shipping_realfee"] = $shipping_realfee;
					$dates[$Newi]["goods_amount"] = $FBAorder['goods_amount'];
				}else{
					//超过1个，所有的金额都平摊
					$allmoneyData = $this->getmoreselllistFBA($FBAorder,$goods_id,count($sell));
					$allmoney = $allmoneyData["allmoney"];
					$dates[$Newi]["costprice"] = $allmoneyData["costprice"];
					$dates[$Newi]["shipping_realfee"] = $allmoneyData['allshipping_realfee'];
					$dates[$Newi]["goods_amount"] = $allmoneyData['goods_amount'];
				}
				if($allmoney >0)
				{
					if($xh==1)
					{
						$dates[$Newi]["isoff"] = 1;//90天外
						$lastMoney = $allmoney * 0.01;
					}else{
						if($FBAorder["instime"]<=$onefishTime)	//90天内10%毛利
						{
							$dates[$Newi]["isoff"] = 0;//90天内
							$lastMoney = $allmoney * 0.1;
						}else{
							$dates[$Newi]["isoff"] = 1;//90天外
							$lastMoney = $allmoney * 0.01;
						}
					}
				}
				if($allmoney <0)
				{
					//小于0，说明订单负数,负数公司和个人各一半
					if($xh==1)
					{
						$dates[$Newi]["isoff"] = 1;//90天外
					}else{
						$dates[$Newi]["isoff"] = 0;//90天内
					}
					$lastMoney = $allmoney /2;
				}
				
				$dates[$Newi]["lastMoney"] = round($lastMoney,2);
				$dates[$Newi]["allmoney"] = round($allmoney,2);
				$dates[$Newi]["shipping_time"] = date("Y-m-d",$FBAorder['instime']);
				$dates[$Newi]["iscome"] =2;	//FBA订单

				//如果计算时间在90天内以10%计算，如果计算时间在90天外以1%计算。需要根据订单来计算

				//查询FBA的相关订单与利润报表

				 $FBAallprofits = $FBAallprofits+$allmoney;//所有利润
				 $FABalllastMoney = $FABalllastMoney+$lastMoney;//利润比

				 $Newi = $Newi+1;
			}
		
			
			$return = 1;

		}

		if($return)
		{
			$allprofits = $allprofits+$FBAallprofits;
			$alllastMoney = $alllastMoney+$FABalllastMoney;
			if($type==0){
				return array("allprofits"=>round($allprofits,2),"alllastMoney"=>round($alllastMoney,2));
			}else{
				return  array("dates"=>$dates,"allprofits"=>round($allprofits,2),"alllastMoney"=>round($alllastMoney,2));
			}
		}else{
			if($type==0)
				return  array("allprofits"=>0,"alllastMoney"=>'0.00');
			else
				return  array("dates"=>$dates,"allprofits"=>0,"alllastMoney"=>'0.00');
		}
	
	}

	function getmoreselllist($order,$goodsID,$nums)
	{
		$allmoney = $allshipping_fee=$allplatform_fee=$allshipping_realfee=0;
		$costprice= 0;
		$sellList = $this->fun->getdatabaseslist("db_order_sell"," AND goods_id in ($goodsID) AND order_id = '".$order['order_id']."'");
	    //店铺
		$rshop = $this->db->get_where("d_shop","id=".intval($order['shopid'])."")->result_array();
		
		
			//平摊

		
			foreach($sellList as $key=>$rs)
			{
				$goods = $this->fun->getdatabasesdetail("db_goods"," AND goods_id='".$rs['goods_id']."'");
				$goods_amount = $rs['goods_amount'];	//如果是多个商品应该是使用商品的销售价


				//如果售价为空

				$goodsmoney = $this->fun->getdatabasesdetail("db_order_sell"," AND  order_id = '".$order['order_id']."' AND goods_amount =0");
				if($goodsmoney)
				{
					//日本，经常有多个商品，只有一个有售价的。按成本比例平摊售价
					$allcosprce =  $this->fun->getdatabasesdetail("db_order_sell"," AND  order_id = '".$order['order_id']."'","sum(costprice) as costprice");
					$actionordercos= $rs['costprice']?$rs['costprice']:$goods['g_costprice'];//成本
					$averageweigth =round($actionordercos/ $allcosprce['costprice'],4);//成本比例
					

					$goods_amount =round( $averageweigth * ($order['goods_amount']),2); // 售价
					$allshipping_fee =round( $averageweigth * ($order['shipping_fee']),2); // 运费收入
					$allplatform_fee = round( $averageweigth * ($order['platform_fee']),2); // 平台费平摊
					
					
					//运费
					if($order['shipping_realfee']>0)
					{
						$shipping_realfee = $order['shipping_realfee'];
					}else{
						$shipping_realfee = $order['shipping_expectfee'];
					}
					$allshipping_realfee = round( $averageweigth * ($shipping_realfee),2); // 物流费用平摊
					$allpay_fee = round( $averageweigth * ($order['pay_fee']),2); // 平台费平摊
					$nprice = round($goods_amount+$allshipping_fee - $allplatform_fee - $allpay_fee - $allshipping_realfee-$actionordercos,2);
					$allmoney = $allmoney+$nprice;
					return array("allmoney"=>$allmoney,"costprice"=>$actionordercos,"goods_amount"=>$goods_amount,"allshipping_fee"=>$allshipping_fee,"allplatform_fee"=>round($allplatform_fee,2),"allshipping_realfee"=>$allshipping_realfee);

				}
				

				$costprice    = $rs['costprice'] * $rs['goods_number']; //总成本

				
				
				$shipping_fee = 0; //需要平摊运费收入
				if($order['shipping_fee']>0)
				{
					$actionorderweight = $order['actionorderweight']?$order['actionorderweight']:$order['orderweight'];//称重
					$averageweigth =round( $order['shipping_fee']/$actionorderweight,4);//单克重量
					$shipping_fee = $averageweigth * ($goods['g_weight'] * $rs['goods_number']); //单克重量*物品重量得出平摊运费
				}

			if($rshop[0]["shop_platform_fee"])
			{
				$fee  =$rshop[0]["shop_platform_fee"];
			
			}else{
				if($order['platformID']==10)
				{
					$fee = 0.1;
				}elseif($order['platformID']==14)
				{
					$fee = 0.1;
				}elseif($order['platformID']==5)
				{
					$fee = 0;
				}elseif($order['platformID']==6)
				{
					$fee = 0.06;
				}elseif($order['platformID']==22)
				{
					$fee = 0.03;
				}else{
					$fee = 0.15;
				}
			}
				$platform_fee =  $goods_amount * $fee;

				
				if($rshop[0]["shop_pay_fee"]>0)
				{
					$fee_pay_fee  =$rshop[0]["shop_pay_fee"];
				}else{
					$fee_pay_fee = 0.015;
				}
				$pay_fee =  $goods_amount * $fee_pay_fee;
				$shipping_realfee = 0;
				//运费
				if($order['shipping_realfee']>0)
				{
					$shipping_realfee = $order['shipping_realfee'];
				}else{
					$shipping_realfee = $order['shipping_expectfee'];
				}

				
				if($shipping_realfee>0)
				{
					$actionorderweight = $order['actionorderweight']?$order['actionorderweight']:$order['orderweight'];//称重
					$averageweigth =round( $shipping_realfee / $actionorderweight,4);//单克重量
					$shipping_realfee = $averageweigth * ($goods['g_weight'] * $rs['goods_number']); //单克重量*物品重量得出平摊运费
				}

				
				
				
				$nprice = round($goods_amount+$shipping_fee - $platform_fee - $pay_fee - $shipping_realfee-$costprice,2);
				$allmoney = $allmoney+$nprice;
				$goodscost =round($goodscost+$costprice,2); //成本
				$allgoods_amount =$allgoods_amount+$goods_amount;
				$allshipping_fee    = $allshipping_fee+$shipping_fee;
				$allplatform_fee = $allplatform_fee+$platform_fee;
				$allshipping_realfee = $allshipping_realfee+$shipping_realfee;
				
		
		}
		return array("allmoney"=>$allmoney,"costprice"=>$goodscost,"goods_amount"=>$allgoods_amount,"allshipping_fee"=>$allshipping_fee,"allplatform_fee"=>round($allplatform_fee,2),"allshipping_realfee"=>$allshipping_realfee);

	}


	function getmoreselllistFBA($order,$goodsID,$nums)
	{
		$allmoney = 0;
		$costprice=$allshipping_realfee=$allgoods_amount=0;
		$sellList = $this->fun->getdatabaseslist("db_amzordertmpsell"," AND goods_id in ($goodsID) AND ordertmpid = '".$order['id']."'");
	
		if(count($sellList) )
		{
			$SellCount = $this->fun->getdatabasesdetail("db_amzordertmpsell"," AND ordertmpid = '".$order['id']."'"," count(id) as count");
			$SellCount = $SellCount["count"];
			//大于1都平摊费用
			foreach($sellList as $key=>$rs)
			{
				$goods = $this->fun->getdatabasesdetail("db_goods"," AND goods_id='".$rs['goods_id']."'");
				
				$goods_amount = $rs['goods_amount'];	//总售价161.95
				$costprice    = $rs['costprice'] ; //总成本62

				
				
				$shipping_fee = 0; //需要平摊运费收入
				if($order['shipping_fee']>0)
				{
					$shipping_fee = round( $order['shipping_fee']/$SellCount,4);//平摊0
				}
				
				
				$pay_fee = round( $order['payship_fee']/$SellCount,4);//负数-52

				
					if($rs['deliverprice']>0)
					{
						$shipping_realfee = $rs['deliverprice'];//头程	12.32
					}else{
						//如果没有头程，重新去计算
						
						$derprice = $this->fun->get_oversea_express_costprice(trim($rs['productid']),$order['shopname']);
						if($derprice>0){
							$shipping_realfee = $derprice*$rs['ordernum'];//头程
						}else{
							$shipping_realfee = 0;
						}

						
					}
				
				$allshipping_realfee = $allshipping_realfee+$shipping_realfee;
				
				$nprice = round($goods_amount+$shipping_fee + $pay_fee - $shipping_realfee-$costprice,2);

				$allmoney = $allmoney+$nprice;
				$goodscost =round($goodscost+$costprice,2); //成本
				$allgoods_amount =  $allgoods_amount+$goods_amount;
				
			}

		
		}
		return array("allmoney"=>$allmoney,"costprice"=>$goodscost,"allshipping_realfee"=>$allshipping_realfee,"goods_amount"=>$allgoods_amount);

	}
	
	function saveexternalBuyDaterefund($cutofftime,$kaideveloper)
	{
		$postdb="";
		$orderData='';
		$where = " AND status = 99 AND del_flag = 0";
		if($kaideveloper)
		{
			$where.=" AND protector ='".$kaideveloper."'";
		}
		$where.=" AND sku in(SELECT p_parent_sku FROM db_goods_parent WHERE p_parent_id >0)";
		$field='B.p_parent_name,sku,finishTime,protector,cutoff_time,cutoff_money,A.id,A.types';
		$orderby="";
		$where.='AND B.p_parent_id >0';
		$list = $this->fun->getdatabaseslist('db_external_buy A left join db_goods_parent B on A.sku = B.p_parent_sku ',$where,$field);
		foreach($list as $key=>$rs)
		{
			$money = $this->externalBuyrefund_Pro($rs['sku']);
			if($money["alllastMoney"])
			{
				if(count($money["dates"])>0)
				{
					$pid = implode(",",$money["dates"]);
					$this->db->update("db_external_report",array("isrefund"=>1),"id in($pid)");
				}
			}
		}
		
	}

	function saveexternalBuyDate($cutofftime,$kaideveloper)
	{
		$i=0;
		$postdb="";
		$orderData='';
		$where = " AND status = 99 AND del_flag = 0";
		if($kaideveloper)
		{
			$where.=" AND protector ='".$kaideveloper."'";
		}
		$where.=" AND sku in(SELECT p_parent_sku FROM db_goods_parent WHERE p_parent_id >0)";
		$field='B.p_parent_name,sku,finishTime,protector,cutoff_time,cutoff_money,A.id,A.types';
		$orderby="";
		$where.='AND B.p_parent_id >0';
		$list = $this->fun->getdatabaseslist('db_external_buy A left join db_goods_parent B on A.sku = B.p_parent_sku ',$where,$field);
		
		
		foreach($list as $key=>$rs)
		{
		
		
			$onefishTime = strtotime($rs["finishTime"])+90*86400;
			//$TwofishTime = strtotime($rs["finishTime"])+365*86400;
			$TwofishTime = strtotime($cutofftime);	//从上次结算到当前 结算时间
			
			//关于结算时间，如果上次结算时间没有，结算时间为上架时间
			if($rs['cutoff_time'] == '0000-00-00')
			{
				$time1 = $rs["finishTime"];
			}else{
				$time1 = $rs['cutoff_time'];	//如果上次结算了，使用上次结算的时间，已结算的订单不再计算
			}

			//从开始到一年后的订单,如果时间在90天内，10%毛利，90天外1%毛利
			$money = $this->getexternalBuySell($rs['sku'],$time1,date("Y-m-d H:i:s",$TwofishTime),$onefishTime,$TwofishTime,1,$rs['types']);
			

			//判断之前是否有记录已结算的值
			$postdb[$key]["cutoff_time"] = $cutofftime;
			$postdb[$key]["cutoff_money"] = $money['alllastMoney']+$rs['cutoff_money'];
			$postdb[$key]["id"] = $rs["id"];
			
			
			//保存订单记录
			
			
			if(count($money['dates']))
			{
			
				foreach($money['dates'] as $keys=>$order)
				{
					$rsdb  = $this->fun->getdatabasesdetail('db_external_report'," AND order_id='".$order['order_id']."'");//如果订单已经核算过，不能再进行核算了
					if(!$rsdb){
						$orderData[$i]["order_id"] = $order['order_id'];
						$orderData[$i]["order_sn"] = $order['order_sn'];
						$orderData[$i]["shipping_time"] = $order['shipping_time'];
						$orderData[$i]["isoff"] = $order['isoff'];//0=90天内,1=90天外
						$orderData[$i]["iscome"] = $order['iscome'];//1直发
						$orderData[$i]["lastMoney"] = $order['lastMoney'];
						$orderData[$i]["allmoney"] = $order['allmoney'];
						$orderData[$i]["protector"] = $rs['protector'];
						$orderData[$i]["sku"] = $rs['sku'];
						$orderData[$i]["external_buy_id"] = $rs['id'];
						$orderData[$i]["cutoff_time"] = $cutofftime;
						$orderData[$i]["cutoff_startime"] = $time1;
						$orderData[$i]["updatetime"] = time();
						$i = $i+1;
					}
				}
			
			}

			
		}
	
		if($orderData){
			$this->db->insert_batch('db_external_report', $orderData);//批量更新
		}
		if($postdb){
		
			$this->db->update_batch('db_external_buy', $postdb, 'id');//批量更新
			//echo $this->db->last_query();
			//die;
		}

	}
	function savenewProductDate($cutofftime,$kaideveloper,$productid)
	{
		$productid = trim($productid,",");
		$this->db->query("UPDATE db_product_develop SET cutoff_time = '".$cutofftime."' WHERE 1 AND status = 6 AND createName ='".$kaideveloper."' AND id in($productid) ");
		
		/*
		$postdb="";
		$where = " AND status = 6 ";
		if($kaideveloper)
		{
			$where.=" AND createName ='".$kaideveloper."'";
		}
		$sql = "SELECT * FROM db_product_develop WHERE 1 ".$where;
		$list=$this->db->query($sql)->result_array();
		foreach($list as $key=>$rs)
		{
			if($rs['cutoff_time']){
				continue;
			}
			if($rs['enddate']<$cutofftime){
				//判断之前是否有记录已结算的值
				$postdb["cutoff_time"] = $cutofftime;
				//$postdb["cutoff_money"] = $rs['cutoff_money'];
				$this->db->update("db_product_develop",$postdb," id = '".$rs['id']."'");
			}

		}
		*/

	}
	function externalBuyrefund_Pro($sku,$type=0)
	{
		if($type==1)
		{
			
		}else{
			$where="and isrefund = 0  ";
		}
		$refundarray=array();
		$alllastMoney = 0;
		$goods_parent = $this->db->get_one("SELECT GROUP_CONCAT(goods_id) as goods_id from db_goods where goods_parent_sku LIKE '".$sku."'");
		$goods_id = $goods_parent["goods_id"];
		if(!$goods_id)
		{
			return array("alllastMoney"=>0,"dates"=>0);
		}
		//Paypal退款
		$sql = "SELECT * FROM db_external_report WHERE sku = '".$sku."' and iscome = 1 $where   AND order_id in(select distinct order_id from db_paypal_refund where refund_status = 3) order by order_id DESC ";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
		
			$refundarray[] = $rs["id"];
			$alllastMoney = $alllastMoney+$rs["lastMoney"];
		}
		
		//亚马逊直发退款
		$sql = "SELECT * FROM db_external_report WHERE sku = '".$sku."' and iscome = 1 $where   AND order_id in(select distinct order_id from db_amzordertmp_cancel where order_status = 5 and order_id >0) order by order_id DESC ";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			$refundarray[] = $rs["id"];
			$alllastMoney = $alllastMoney+$rs["lastMoney"];
		}
		//Wish退款
		$sql = "SELECT * FROM db_external_report WHERE sku = '".$sku."' and iscome = 1 $where  AND  order_id in(select distinct order_id from db_wish_refund where goods_id in ($goods_id)) order by order_id DESC ";
		
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			$refundarray[] = $rs["id"];
			$alllastMoney = $alllastMoney+$rs["lastMoney"];
		}
		//亚马逊FBA退款
		$sql = "SELECT * FROM db_external_report WHERE sku = '".$sku."' and iscome = 2 $where   AND  order_id in(select distinct amazon_id from db_amzordertmp_refund where goods_id in ($goods_id) and amazon_id >0) order by order_id DESC ";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			$refundarray[] = $rs["id"];
			$alllastMoney = $alllastMoney+$rs["lastMoney"];
		}
		
		return array("alllastMoney"=>$alllastMoney,"dates"=>$refundarray);
	}


	function externalBuyrefund($sku,$time1,$time2,$onefishTime,$TwofishTime,$type=0,$xh=0)
	{
	
		$refundlist = array();
		$alllastMoney = 0;
		//Paypal退款
		$goods_parent = $this->db->get_one("SELECT GROUP_CONCAT(goods_id) as goods_id from db_goods where goods_parent_sku LIKE '".$sku."'");
		$goods_id = $goods_parent["goods_id"];

		$time1 = "2017-10-10 08:42:26";
		$sql = "SELECT order_id FROM db_order_list where  (order_status = 5 or order_status = 4) AND  shipping_time >='".strtotime($time1)."' AND shipping_time <='".strtotime($time2)."' and order_id in(select distinct order_id from db_order_sell where goods_id in ($goods_id)) AND order_id in(select distinct order_id from db_paypal_refund where refund_status = 3) order by order_id DESC";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			$money = $this->getexternalBuySell($sku,$time1,date("Y-m-d H:i:s",$TwofishTime),$onefishTime,$TwofishTime,1,$type,$rs["order_id"]);
			if(count($money['dates'])>0){
				
				foreach($money['dates'] as $dskey=>$rsdb)
				{
					if($rsdb["iscome"]==1)
					{
						$dates[$key]["lastMoney"] = $rsdb["lastMoney"];
						$alllastMoney = $alllastMoney+$rsdb["lastMoney"];
					}
				}
			}
		}

		//亚马逊直发退款
		$Newi = count($dates)+1;
		$sql = "SELECT order_id FROM db_order_list where 1 and  shipping_time >='".strtotime($time1)."' AND shipping_time <='".strtotime($time2)."'  and order_id in(select distinct order_id from db_order_sell where goods_id in ($goods_id)) AND order_id in(select distinct order_id from db_amzordertmp_cancel where order_status = 5) order by order_id DESC";
		$query = $this->db->query($sql);
		$zhiFdates = $query->result_array();
		foreach($zhiFdates as $key=>$rs)
		{
			$money = $this->getexternalBuySell($sku,$time1,date("Y-m-d H:i:s",$TwofishTime),$onefishTime,$TwofishTime,1,$type,$rs["order_id"]);
			if(count($money['dates'])>0){
				foreach($money['dates'] as $dskey=>$rsdb)
				{
					if($rsdb["iscome"]==1)
					{
						$dates[$Newi]["lastMoney"] = $rsdb["lastMoney"];
						$alllastMoney = $alllastMoney+$rsdb["lastMoney"];
					}
				}
			}
			$Newi = $Newi+1; 
		}
		
		
	
		//Wish退款
		$Newist = count($dates)+1;
		$sql = "SELECT order_id FROM db_order_list where 1 AND  shipping_time >='".strtotime($time1)."' AND shipping_time <='".strtotime($time2)."' and order_id in(select distinct order_id from db_order_sell where goods_id in ($goods_id)) AND order_id in(select distinct order_id from db_wish_refund where 1) order by order_id DESC";
		$query = $this->db->query($sql);
		$zhiFdates = $query->result_array();
		foreach($zhiFdates as $key=>$rs)
		{
			$money = $this->getexternalBuySell($sku,$time1,date("Y-m-d H:i:s",$TwofishTime),$onefishTime,$TwofishTime,1,$type,$rs["order_id"]);
			if(count($money['dates'])>0){
				foreach($money['dates'] as $dskey=>$rsdb)
				{
					if($rsdb["iscome"]==1)
					{
						$dates[$Newist]["lastMoney"] = $rsdb["lastMoney"];
						$alllastMoney = $alllastMoney+$rsdb["lastMoney"];
					}
				}
			}
			$Newist = $Newist+1; 
		}
		
		
		//亚马逊FBA退款
		$Newists = count($dates)+1;
		$sql = "SELECT * FROM db_amzordertmp where (status = 1) and instime >='".strtotime($time1)."' AND instime <='".strtotime($time2)."' and id in(select distinct amazon_id from db_amzordertmp_refund where goods_id in ($goods_id) and amazon_id >0) order by id DESC";
		$query = $this->db->query($sql);
		$zhiFdates = $query->result_array();
		foreach($zhiFdates as $key=>$rs)
		{
			print_r($rs);
			$money = $this->getexternalBuySell($sku,$time1,date("Y-m-d H:i:s",$TwofishTime),$onefishTime,$TwofishTime,1,$type,$rs["id"],1);
			if(count($money['dates'])>0){
				foreach($money['dates'] as $dskey=>$rsdb)
				{
					if($rsdb["iscome"]==2)
					{
						$dates[$Newists]["lastMoney"] = $rsdb["lastMoney"];
						$alllastMoney = $alllastMoney+$rsdb["lastMoney"];
					}
				}
			}
			$Newists = $Newists+1; 
		}
	
		//print_r($dates);
	
		return array("alllastMoney"=>$alllastMoney);
		
	}
}
/* End of file Datareport_m.php */